<?php

namespace App\Http\Controllers;

use App\Models\OpenCity;
use App\Models\WechatUser;
use Illuminate\Support\Facades\DB;

class OpenCityController extends Controller
{
    public function getActiveCity()
    {
        $location = request()->input('location');
        $userId = request()->input('user_id', 0);
        $user = WechatUser::find($userId);
        if ($user) {
            if (empty($user->city)) {
                $user->city = $location;
                $user->save();
            }
        }

        $city = OpenCity::where('key', $location)->first();
        if ($city) {
            return response()->json(['recode' => 1, 'res' => ['active' => $city->key, 'name' => $city->City->name]]);
        }
        $location = substr($location, 0, 4) . '00';
        $city = OpenCity::where('key', $location)->first();
        if ($city) {
            return response()->json(['recode' => 1, 'res' => ['active' => $city->key, 'name' => $city->City->name]]);
        }
        $location = substr($location, 0, 2) . '0000';
        $city = OpenCity::where('key', $location)->first();
        if ($city) {
            return response()->json(['recode' => 1, 'res' => ['active' => $city->key, 'name' => $city->City->name]]);
        }
        $city = OpenCity::where('default', 1)->first();
        if ($city) {
            return response()->json(['recode' => 1, 'res' => ['active' => $city->key, 'name' => $city->City->name]]);
        }
        return response()->json(['recode' => 0, 'msg' => '未找到城市']);
    }

    public function getOpenCityList()
    {
        $id = request()->input('id');
        if ($id==0){
            $list=DB::select('(SELECT c.key,c.remark name,h.id,h.pid FROM open_cities c LEFT JOIN hup_region h on c.remark=h.name where pid=0  GROUP BY name ORDER BY pid asc)
UNION ALL
(SELECT c.key, c.name,c.id, c.pid
FROM (SELECT c.key,c.remark name,h.id,h.pid FROM open_cities c LEFT JOIN hup_region h on c.remark=h.name GROUP BY name ORDER BY pid asc) c
JOIN (
    SELECT id
    FROM (SELECT c.key,c.remark name,h.id,h.pid FROM open_cities c LEFT JOIN hup_region h on c.remark=h.name GROUP BY name ORDER BY pid asc) a
    WHERE pid IS NULL OR pid = 0
) p ON c.pid = p.id)
ORDER BY id');
//            $list = OpenCity::join('city_lists', 'open_cities.key', '=', 'city_lists.key')
//                ->where('city_lists.level', 0)
//                ->selectRaw("city_lists.`key` ,city_lists.`name`")
//                ->get();
        }else{
            $list=DB::select("SELECT c.key,c.remark name,h.id,h.pid FROM open_cities c LEFT JOIN hup_region h on c.remark=h.name where pid=$id or h.id=$id GROUP BY name ORDER BY pid asc");
        }

        return response()->json(['recode' => 1, 'list' => $list]);

    }
}
